const db = require('../model/database')
const query = require('../model/query')

// 查询所有学院
exports.getCollegeList = async (req, res) => {
  // 查询学院信息
  const sql = 'SELECT * FROM college'
  const result = await query(sql, null, db)
  // 查询学院人数
  const sql2 = 'SELECT college, COUNT(*) AS count FROM student GROUP BY college'
  const result2 = await query(sql2, null, db)
  // 将学院人数添加到学院信息中
  result.forEach(item => {
    const count = result2.find(item2 => item2.college === item.id)
    item.total = count ? count.count : 0
  })

  res.send({code: 20000, msg: '查询成功', data: result})
}

// 修改学院信息
exports.updateCollege = async (req, res) => {
  const collegeInfo = req.body
  const id = collegeInfo.id
  delete collegeInfo.id
  const sql = 'UPDATE college SET ? WHERE id = ?'
  const result = await query(sql, [collegeInfo, id], db)
  res.send({code: 20000, msg: '修改成功', data: result})
}

// 添加学院
exports.addCollege = async (req, res) => {
  const collegeInfo = req.body
  // 必须要有学院名称
  if (!collegeInfo.name) {
    res.send({code: 40000, msg: '学院名称不能为空'})
    return
  }
  // 学院名称不能重复
  const sql1 = 'SELECT * FROM college WHERE name = ?'
  const result1 = await query(sql1, collegeInfo.name, db)
  if (result1.length > 0) {
    res.send({code: 40000, msg: '学院名称不能重复'})
    return
  }

  const sql = 'INSERT INTO college SET ?'
  const result = await query(sql, collegeInfo, db)
  res.send({code: 20000, msg: '添加成功', data: result})
}

// 删除学院
exports.deleteCollege = async (req, res) => {
  const id = req.params.id
  // 学院下有 学生 不能删除
  const sql1 = 'SELECT * FROM student WHERE college = ?'
  const result1 = await query(sql1, id, db)
  if (result1.length > 0) {
    res.send({code: 40000, msg: '学院下有学生，不能删除'})
    return
  }
  // 学院下有 专业 不能删除
  const sql2 = 'SELECT * FROM major WHERE college_id = ?'
  const result2 = await query(sql2, id, db)
  if (result2.length > 0) {
    res.send({code: 40000, msg: '学院下有专业，不能删除'})
    return
  }

  // 删除学院
  const sql = 'DELETE FROM college WHERE id = ?'
  const result = await query(sql, id, db)
  res.send({code: 20000, msg: '删除成功', data: result})
}

